package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.shsh.safemanage.model.User;

public class UserDao {
	public User login(String jobNum, String password) {
		String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM]," +
				"employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE," +
				"employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME," +
				"employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME," +
				"[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID," +
				"[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED] " +
				"FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID " +
				"left outer join department2 on employee.DEPARTMENT2_ID=department2.ID " +
				"left outer join department3 on employee.DEPARTMENT3_ID=department3.ID " +
				"left outer join job_post on employee.JOB_POST1_ID=job_post.ID " +
				"left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID " +
				"where JOB_NUM=? and LOGIN_PASSWORD=? and IS_REGISTERED=1";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, jobNum);
			pstm.setString(2, password);
			
			ResultSet rs = pstm.executeQuery();
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
			}
			
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public User getUserByID(Long id) {
		String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM]," +
				"employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE," +
				"employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME," +
				"employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME," +
				"[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID," +
				"[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED] " +
				"FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID " +
				"left outer join department2 on employee.DEPARTMENT2_ID=department2.ID " +
				"left outer join department3 on employee.DEPARTMENT3_ID=department3.ID " +
				"left outer join job_post on employee.JOB_POST1_ID=job_post.ID " +
				"left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID " +
				"where employee.ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			User user = null;
			if (rs.next()) {
				user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
			}
			
			return user;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public User getUser(String name, String jobNum) {
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = ConnectionManager.getSafeManageConn();
			String sql = "SELECT employee.[ID] as ID,[NAME],[PERSON_NUM],[JOB_NUM]," +
					"employee.[DEPARTMENT1_ID] as DEPARTMENT1_ID,department1.DEPARTMENT1_NAME as DEPARTMENT1_NAME,DEPARTMENT_TYPE," +
					"employee.[DEPARTMENT2_ID] as DEPARTMENT2_ID,department2.DEPARTMENT2_NAME as DEPARTMENT2_NAME," +
					"employee.[DEPARTMENT3_ID] as DEPARTMENT3_ID,department3.DEPARTMENT3_NAME as DEPARTMENT3_NAME," +
					"[JOB_POST1_ID],job_post.POST_NAME as POST1_NAME,[JOB_POST2_ID],[JOB_POST3_ID],employee.[JOB_TITLE_ID] as JOB_TITLE_ID," +
					"[MANAGE_GROUP_ID],manage_group.MANAGE_GROUP_NAME as MANAGE_GROUP_NAME,[LOGIN_PASSWORD],[IS_REGISTERED] " +
					"FROM [employee] left outer join department1 on employee.DEPARTMENT1_ID=department1.ID " +
					"left outer join department2 on employee.DEPARTMENT2_ID=department2.ID " +
					"left outer join department3 on employee.DEPARTMENT3_ID=department3.ID " +
					"left outer join job_post on employee.JOB_POST1_ID=job_post.ID " +
					"left outer join manage_group on employee.MANAGE_GROUP_ID=manage_group.ID ";
			String sql1 = null;
			
			if (jobNum == null || jobNum.length() == 0) {
				if (name == null || name.length() == 0) {
					return null;
				} else {
					sql1 = "where employee.NAME=?";
					pstm = conn.prepareStatement(sql + sql1);
					pstm.setString(1, name);
				}
			} else {
				sql1 = "where employee.JOB_NUM=?";
				pstm = conn.prepareStatement(sql + sql1);
				pstm.setString(1, jobNum);
			}
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setPersonNum(rs.getString("PERSON_NUM"));
				user.setJobNum(rs.getString("JOB_NUM"));
				user.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				user.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				user.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				user.setDepartment3ID(rs.getLong("DEPARTMENT3_ID"));
				user.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost1Name(rs.getString("POST1_NAME"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				user.setJobTitleID(rs.getLong("JOB_TITLE_ID"));
				user.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));
				user.setManageGroupName(rs.getString("MANAGE_GROUP_NAME"));
				user.setRegistered(rs.getBoolean("IS_REGISTERED"));
				
				rs.close();
				
				if (user.getJobPost2ID() != null) {
					sql = "select POST_NAME from job_post where ID=?";
					pstm = conn.prepareStatement(sql);
					pstm.setLong(1, user.getJobPost2ID());
					
					rs = pstm.executeQuery();
					if (rs.next()) {
						user.setJobPost2Name(rs.getString(1));
					}
					rs.close();
					
					if (user.getJobPost3ID() != null) {
						sql = "select POST_NAME from job_post where ID=?";
						pstm = conn.prepareStatement(sql);
						pstm.setLong(1, user.getJobPost3ID());
						
						rs = pstm.executeQuery();
						if (rs.next()) {
							user.setJobPost3Name(rs.getString(1));
						}
						rs.close();
					}
				}
				
				return user;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public int getUserTitleLevel(Long titleLevelID) {
		String sql = "select TITLE_LEVEL from job_title where ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, titleLevelID);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return rs.getInt("TITLE_LEVEL");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return 0;
	}
	
	public boolean adminLogin(String adminName, String password) {
		String sql = "select * from admin where ADMIN_NAME=? and ADMIN_PW=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, adminName);
			pstm.setString(2, password);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
	
	public boolean updateUserInfo(User registerUser) {
		if (registerUser == null) {
			return false;
		}
		
		String isRegisterSQL = "select IS_REGISTERED from employee where ID=?";
		String sql = "update employee set DEPARTMENT1_ID=?, DEPARTMENT2_ID=?, DEPARTMENT3_ID=?, " +
				"JOB_POST1_ID=?, JOB_POST2_ID=?, JOB_POST3_ID=?, JOB_TITLE_ID=?, MANAGE_GROUP_ID=?, LOGIN_PASSWORD=?, IS_REGISTERED=? " +
				"where ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(isRegisterSQL);
			pstm.setLong(1, registerUser.getId());
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				if (rs.getBoolean(1)) {
					return false;
				}
			} else {
				return false;
			}
			
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, registerUser.getDepartment1ID());
			pstm.setLong(2, registerUser.getDepartment2ID());
			pstm.setLong(3, registerUser.getDepartment3ID());
			pstm.setLong(4, registerUser.getJobPost1ID());
			pstm.setLong(5, registerUser.getJobPost2ID());
			pstm.setLong(6, registerUser.getJobPost3ID());
			pstm.setLong(7, registerUser.getJobTitleID());
			pstm.setLong(8, registerUser.getManageGroupID());
			pstm.setString(9, registerUser.getPassword());
			pstm.setBoolean(10, Boolean.TRUE);
			pstm.setLong(11, registerUser.getId());
			
			pstm.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
	
	public List<User> getManageGroupMappingUserList(Long manageGroupID) {
		List<User> userList = new ArrayList<User>();
		String sql = "select ID, NAME, JOB_POST1_ID, JOB_POST2_ID, JOB_POST3_ID from employee where MANAGE_GROUP_ID=?";
		String sql1 = "select role.ROLE_TYPE from job_post left outer join role on job_post.ROLE_ID=role.ID " +
				"where job_post.ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(sql1);
			
			pstm.setLong(1, manageGroupID);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setJobPost1ID(rs.getLong("JOB_POST1_ID"));
				user.setJobPost2ID(rs.getLong("JOB_POST2_ID"));
				user.setJobPost3ID(rs.getLong("JOB_POST3_ID"));
				
				pstm1.setLong(1, user.getJobPost1ID());
				ResultSet rs1 = pstm1.executeQuery();
				if (rs1.next()) {
					user.setRoleType(user.getRoleType() | rs1.getInt("ROLE_TYPE"));
				}
				
				pstm1.setLong(1, user.getJobPost2ID());
				rs1 = pstm1.executeQuery();
				if (rs1.next()) {
					user.setRoleType(user.getRoleType() | rs1.getInt("ROLE_TYPE"));
				}
				
				pstm1.setLong(1, user.getJobPost3ID());
				rs1 = pstm1.executeQuery();
				if (rs1.next()) {
					user.setRoleType(user.getRoleType() | rs1.getInt("ROLE_TYPE"));
				}
				
				userList.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return userList;
	}
	
	public List<User> getUsersByRoleType(Long department1ID, int roleType) {
		String sql = "select employee.ID, NAME, employee.DEPARTMENT1_ID, JOB_POST1_ID, JOB_POST2_ID, JOB_POST3_ID, ROLE_TYPE " +
				"from employee left outer join job_post on employee.JOB_POST1_ID=job_post.ID or employee.JOB_POST2_ID=job_post.ID or employee.JOB_POST3_ID=job_post.ID " +
				"left outer join role on job_post.ROLE_ID=role.ID " +
				"where employee.DEPARTMENT1_ID=? and role.ROLE_TYPE=?";
		List<User> userList = new ArrayList<User>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, department1ID);
			pstm.setInt(2, roleType);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				user.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				user.setRoleType(rs.getInt("ROLE_TYPE"));
				
				userList.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return userList;
	}
}
